Imports System.Data.SqlClient

Friend Class DBScanTable
    Private moServer As SQLAutoDocLib.Server
    Private moDatabase As SQLAutoDocLib.Database

    Public Event ItemScanStart(ByVal lServerID As Long, ByVal lDatabaseID As Long, ByVal lSessionID As Long, ByVal lTableID As Long)
    Public Event ItemScanComplete(ByVal lSessionID As Long, ByVal lTableID As Long, ByVal oEndDate As Date)

    Public Sub New( _
                ByVal oServer As SQLAutoDocLib.Server, _
                ByVal oDatabase As SQLAutoDocLib.Database)

        moServer = oServer
        moDatabase = oDatabase
    End Sub

    Public Function Scan(ByVal lSessionID As Long) As Boolean
        Dim bChangesFound As Boolean = False

        Dim oFactory As New Table_Factory
        Dim oAllTables As Collection = oFactory.GetAllTablesForDatabase(moDatabase.ID)

        For Each oTable As SQLAutoDocLib.Table In oFactory.GetAllTablesForDatabase(moDatabase.ID)
            oTable.IsDeleted = True
            oTable.Save()
        Next

        Dim iAlteredTables As Integer = 0
        Dim bTablesAddedOrUpdated As Boolean = False
        RefreshTablesForDatabase(lSessionID, oAllTables, iAlteredTables)
        If iAlteredTables > 0 Then bTablesAddedOrUpdated = True

        If bTablesAddedOrUpdated = False Then
            '---Were any tables deleted?
            For Each oTable As SQLAutoDocLib.Table In oAllTables
                If oTable.IsDeleted = True Then
                    oTable.DatabaseRevID = moDatabase.RevID
                    oTable.Save(bForceAdd:=True)
                    bChangesFound = True
                End If
            Next
        Else
            bChangesFound = True
        End If

        Return bChangesFound
    End Function

    Private Sub RefreshTablesForDatabase( _
                            ByVal lSessionID As Long, _
                            ByRef oAllTables As Collection, _
                            ByRef iAlteredTables As Integer)

        Dim sSQL = "select distinct"
        sSQL = sSQL & "     a.id,"
        sSQL = sSQL & "     b.name 'TableOwner',"
        sSQL = sSQL & "     a.name 'TableName'"
        sSQL = sSQL & " from "
        sSQL = sSQL & "     dbo.sysobjects a"
        sSQL = sSQL & "         join dbo.sysusers b on b.uid=a.uid"
        sSQL = sSQL & " where"
        sSQL = sSQL & "     a.type = 'u'"
        sSQL = sSQL & "     and a.category!=2"
        sSQL = sSQL & " order by"
        sSQL = sSQL & "     b.[name],"
        sSQL = sSQL & "     a.[name]"

        Dim oCon As New SqlConnection(moDatabase.DecryptedConnectionString)
        oCon.Open()

        Dim oCMD As New SqlCommand(sSQL, oCon)
        Dim oDT As New SqlDataAdapter(oCMD)

        Dim oData As New DataTable

        oDT.Fill(oData)

        oDT.Dispose()
        oCMD.Dispose()
        oCon.Close()
        oCon.Dispose()

        oDT.Dispose()

        Dim oLoginFactory As New Login_Factory
        Dim oTableFactory As New Table_Factory

        Dim bTableAdded As Boolean = False

        For Each oRow As DataRow In oData.Rows
            Dim oLogin As SQLAutoDocLib.Login = oLoginFactory.GetLoginByName(moDatabase.ID, oRow.Item("TableOwner"))
            Dim oTable As SQLAutoDocLib.Table = oTableFactory.GetTableByName(moDatabase.ID, oLogin.ID, oRow.Item("TableName"))

            If oTable Is Nothing Then
                '---Found a new table
                bTableAdded = True

                oTable = New SQLAutoDocLib.Table
                With oTable
                    .Name = oRow.Item("TableName")
                    .OwnerID = oLogin.ID
                    .OID = oRow.Item("id")
                    .DatabaseID = moDatabase.ID
                    .DatabaseRevID = moDatabase.RevID
                End With

                iAlteredTables = iAlteredTables + 1
            End If

            If oAllTables.Contains(CStr(oTable.ID)) Then
                oAllTables(CStr(oTable.ID)).IsDeleted = False
            End If

            oTable.IsDeleted = False

            oTable.Save()

            RaiseEvent ItemScanStart(moDatabase.ServerID, moDatabase.ID, lSessionID, oTable.ID)

            oTable.DatabaseRevID = moDatabase.RevID
            Dim bFieldsUpdated As Boolean = oTable.Scan()

            If bFieldsUpdated = True Then
                iAlteredTables = iAlteredTables + 1
            End If

            RaiseEvent ItemScanComplete(lSessionID, oTable.ID, Now)

        Next
    End Sub
End Class
